#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   sku2_level_e_price_process.py    
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2021-08-03 11:20   pengwei.sun      1.0         None
'''
import os
import sys

sys.path.append(os.getcwd())
from src.utils.config import logger
import pandas as pd
import numpy as np
import time, datetime
from src.utils.db_processor import mysql_prediction_processor
from src.utils.util import get_today, check_date_str, format_date_string, format_date
from src.tablet.levelrate.reverse.function_utils import save_pickle_data, load_pickle_data
from src.tablet.levelrate.reverse.sku2_common_variable import insert_mysql_columns
from src.tablet.levelrate.reverse.sku2_inverse_level_reverse_multi_thread_tablet import process1_level_inverse_fun
from src.tablet.levelrate.reverse.sku2_inverse_level_reverse2_multi_thread_tablet import process3_level_inverse_fun
from src.tablet.levelrate.reverse.sku2_inverse_rate_calculation_multi_thread_v1_tablet import process2_property_inverse_fun
from src.tablet.levelrate.reverse.sku2_inverse_rate_calculation2_multi_thread_v1_tablet import process4_property_inverse_fun
from src.tablet.levelrate.reverse.sku2_common_variable import FILE_DIR
from src.utils.feishu_message import feishu_messager

# E以下等级的下压处理比率
level_list = ['G', 'H', 'I', 'J', 'K']
#old
# level_rate_dict = {
#     'H': 0.6,
#     'I': 0.5,
#     'J': 0.4,
#     'K': 0.35}

#2023-04-12 瑞楠根据平板特性进行调整
level_rate_dict = {
    'H': 0.5,
    'I': 0.45,
    'J': 0.40,
    'K': 0.35}

# 查询sku是否为大陆国行
purchase_query_sql = """
select distinct
a.product_sku_id as product_sku_key,purchase_channel_name,
case when purchase_channel_name='大陆国行（苹果系统型号M开头）' then 1 else 0 end purchase_flag
 from sku2_proprerty_value_column a
"""

CHECK_COLUMNS_LIST = ['product_category_id', 'product_category_name', 'product_brand_id',
                      'product_brand_name', 'product_key', 'product_name', 'product_sku_key',
                      'product_sku_name', 'product_level_name', 'level_sub', 'template_rank',
                      'rank', 'product_level_key', 'process_price_res', 'predict_origin',
                      'forecast_reference_price', 'y_pred', 'price_0_7', 'sale_num_0_7','thisprice',
                      'process_price', 'process_price_inverse_begin', 'final_price', 'saleprice',
                      'price_8_14', 'sale_num_8_14', 'price_15_21', 'sale_num_15_21', 'price_22_42',
                      'sale_num_22_42', 'count', 'sumprice', 'avgprice',
                      'price_3', 'price_2', 'price_1', 'qty', 'mean_rate',
                      'reverse', 'reverse_cnt', 'process_price_last', 'sku_e_level_min_rank',
                      'sku_level_max_price', 'sku_e_level_min_price',
                      'limit_flag', 'limit_rate', 'concat_leve_rank', 'level_limit_cnt',
                      'limit_max_rate', 'limit_min_rate', 'product_level_name_a2', 'a2_price',
                      'purchase_channel_name', 'purchase_flag']

"""
E以下等级下压价格处理
"""
def level_limit_price_fun(row):
    level = row['level_sub']
    limit_price = row['sku_e_level_min_price']
    max_price = row['sku_level_max_price']
    process_price_last = row['process_price_last']
    sale_num_0_7 = row['sale_num_0_7']
    rate = level_rate_dict.get(level, -1)
    if sale_num_0_7>0 and level not in ['I', 'J', 'K']:
        return process_price_last

    if level in level_list:
        # 针对F,G等级进行e的最小价下压处理
        if rate == -1:
            if limit_price < process_price_last:
                return limit_price * 0.99
            else:
                return process_price_last

        limit_p = max_price * rate

        if limit_price < process_price_last or limit_p < process_price_last:
            return min(limit_price * 0.99, limit_p)

    return process_price_last


"""
计算E以下等级是否按照规则进行了下压处理
"""
def level_limit_flag_fun(row):
    level = row['level_sub']
    limit_price = row['sku_e_level_min_price']
    max_price = row['sku_level_max_price']
    process_price_last = row['process_price_last']
    rate = level_rate_dict.get(level, -1)
    if level in level_list:
        if rate == -1:
            if limit_price < process_price_last:
                return 1
            else:
                return 0
        limit_p = max_price * rate
        if limit_price < process_price_last or limit_p < process_price_last:
            return 1

    return 0


"""
计算E以下等级的下压的比率
"""
def level_limit_rate_fun(row):
    level = row['level_sub']
    limit_price = row['sku_e_level_min_price']
    max_price = row['sku_level_max_price']
    process_price_last = row['process_price_last']
    rate = level_rate_dict.get(level, -1)
    if level in level_list:
        if rate == -1: return 1
        limit_p = max_price * rate
        if limit_price < process_price_last or limit_p < process_price_last:
            if min(limit_price, limit_p)==0:
                return 1
            return process_price_last / min(limit_price, limit_p)

    return 1


"""
处理S ,A+ 与A2等级的关系，进行一定的折扣处理
2. 所有手机的非大陆国行的S等级价格偏高，大陆国行S等级出货价很高所以bi价高一些，但是非大陆国行的S等级不值钱，运用了大陆国行的等级比不合适。
决定在最后一步处理：大陆国行S的上限阈S=A2*1.5，非大陆国行S的上限阈S=A2*1.2
3. 最后一步处理A+=A2*1.01，所有含A2的A+都要这么处理
"""
def S_A_base_a2_fun(row):
    level = row['product_level_name']
    a2_price = row['a2_price']
    final_price = row['final_price']
    purchase_flag = row['purchase_flag']
    res_price = final_price

    if np.isnan(a2_price):
        return res_price

    rate = 1.2
    if purchase_flag == 1:
        rate = 1.5
    if level == 'S' and a2_price > 0:
        res_price = min(a2_price * rate, final_price)
    # elif level=='A+' and a2_price >0:
    #     res_price=min(a2_price*1.01,final_price)

    return res_price


def combine_reverse_fun():

    t1 = time.time()
    res1=process1_level_inverse_fun()
    logger.info('process process1_level_inverse_fun use time @{}'.format((time.time() - t1) / 60))
    t1 = time.time()
    res2=process2_property_inverse_fun()
    logger.info('process process2_property_inverse_fun use time @{}'.format((time.time() - t1) / 60))
    t1 = time.time()
    # res3=process3_level_inverse_fun()
    logger.info('process process3_level_inverse_fun use time @{}'.format((time.time() - t1) / 60))
    t1 = time.time()
    # reverse_df = process4_property_inverse_fun()
    logger.info('process process4_property_inverse_fun use time @{}'.format((time.time() - t1) / 60))
    t1 = time.time()
    logger.info('combine_reverse_fun end !')



def process_e_price():
    reverse_1_df = load_pickle_data(FILE_DIR + 'sku2_price_property_reverse_calculation_process2_df_tablet.pkl')
    reverse_1_df['process_price_cal'] = reverse_1_df['process_price'].copy()
    inverse_df = load_pickle_data(FILE_DIR + 'sku2_price_level_reverse_process1_data_tablet.pkl')
    reverse_df1=inverse_df
    fetch_df = load_pickle_data(FILE_DIR + 'sku2_price_fetch_process_data_tablet.pkl')

    purchase_df = mysql_prediction_processor.load_sql(purchase_query_sql)
    purchase_df['product_sku_key'] = purchase_df['product_sku_key'].astype(int)

    inverse_df['process_price_tmp_b'] = inverse_df['process_price_tmp']
    inverse_df['process_price_b'] = inverse_df['process_price']
    fetch_df['process_price_res'] = fetch_df['process_price']

    fetch_df['level_sub'] = fetch_df['product_level_name'].str[0:1]

    reverse_df1.drop(['level_sub', 'product_level_name', 'template_rank', 'rank'], axis=1, inplace=True)

    inverse_df[['product_sku_key', 'product_level_key']] = inverse_df[['product_sku_key', 'product_level_key']].apply(
        np.int64)
    reverse_df1[['product_sku_key', 'product_level_key']] = reverse_df1[['product_sku_key', 'product_level_key']].apply(
        np.int64)
    fetch_df[['product_sku_key', 'product_level_key']] = fetch_df[['product_sku_key', 'product_level_key']].apply(
        np.int64)
    reverse_1_df[['product_sku_key', 'product_level_key']] = reverse_1_df[['product_sku_key', 'product_level_key']].apply(
        np.int64)


    reverse_df1 = pd.merge(reverse_df1, inverse_df[
        ['product_sku_key', 'product_level_key', 'process_price_b', 'process_price_tmp_b']], how='left',
                           on=['product_sku_key', 'product_level_key'])
    reverse_df1['reverse']=0
    reverse_df1 = pd.merge(fetch_df[['product_category_id', 'product_category_name', 'product_brand_id',
                                     'product_brand_name', 'product_key', 'product_name', 'product_sku_name',
                                     'product_sku_key', 'product_level_name', 'level_sub',
                                     'template_rank', 'rank', 'product_level_key', 'process_price_res',
                                     'predict_origin', 'forecast_reference_price',
                                     'price_0_7', 'sale_num_0_7', 'price_8_14', 'sale_num_8_14', 'price_15_21',
                                     'sale_num_15_21',
                                     'price_22_42', 'sale_num_22_42', 'count', 'sumprice', 'avgprice', 'thisprice',
                                     'price_3', 'price_2', 'price_1', 'saleprice', 'qty', 'mean_rate']],

                           reverse_df1[
                               ['product_sku_key', 'product_level_key', 'process_price', 'process_price_inverse_begin',
                                'reverse', 'reverse_cnt']],
                           how='left', on=['product_sku_key', 'product_level_key'])

    reverse_df1 = reverse_df1.merge(reverse_1_df[['product_sku_key', 'product_level_key','process_price_cal']],how='left',on=['product_sku_key', 'product_level_key'])
    reverse_df1['process_price_level'] = reverse_df1['process_price'].copy()
    reverse_df1['process_price'] = reverse_df1['process_price_cal'].copy()
    reverse_df1['process_price_last'] = reverse_df1['process_price']

    reverse_df1.loc[reverse_df1.process_price.isnull(), 'process_price_last'] = reverse_df1.loc[
        reverse_df1.process_price.isnull(), 'process_price_res']

    match_level_price = reverse_df1.loc[reverse_df1.level_sub.isin(['A', 'B', 'C', 'D'])]
    grouped = match_level_price.groupby(by='product_sku_key')['process_price_last'].agg(
        {'sku_level_max_price': 'max'}).reset_index()  # 这里

    # 获取E等级的最小等级排序
    e_level_min_rank = reverse_df1.loc[reverse_df1.level_sub.isin(['E'])].groupby(by='product_sku_key')[
        'template_rank'].agg({'sku_e_level_min_rank': 'min'}).reset_index()  # 这里
    reverse_df1 = reverse_df1.merge(e_level_min_rank, how='inner', on='product_sku_key')

    # 获取e等级的最小价格
    grouped_e = reverse_df1.loc[
        (reverse_df1.level_sub.isin(['E'])) & (reverse_df1.template_rank == reverse_df1.sku_e_level_min_rank)].groupby(
        by='product_sku_key')['process_price_last'].agg({'sku_e_level_min_price': 'min'}).reset_index()  # 这里
    grouped = grouped.merge(grouped_e, on=['product_sku_key'])

    # E等级最小价格 拼接进最终输出数据中
    reverse_df1 = reverse_df1.merge(grouped, how='left', on='product_sku_key')

    # E以下等级 假如规则处理
    match_E_level_price = reverse_df1.loc[~reverse_df1.level_sub.isin(['S', 'A', 'B', 'C', 'D'])]
    match_E_level_price['final_price'] = match_E_level_price.apply(level_limit_price_fun,
                                                                   axis=1)  # H,I,J,K等级满足等级最小比率处理；小于e等级最小价格处理
    match_E_level_price['limit_flag'] = match_E_level_price.apply(level_limit_flag_fun, axis=1)  # 标识出是否进行了规则处理 sku_level_max_price

    # match_E_level_price.loc[match_E_level_price.sku_e_level_min_price==0,'sku_e_level_min_price']=match_E_level_price.loc[match_E_level_price.sku_e_level_min_price==0,'sku_level_max_price']
    match_E_level_price['limit_rate'] = match_E_level_price.apply(level_limit_rate_fun, axis=1)  # 标识出处理下压的价格比率

    df = match_E_level_price.loc[match_E_level_price.limit_flag > 0]  # 筛选出进行规则处理的数据
    df['concat_leve_rank'] = df['product_sku_key'].astype(str) + '_' + df['level_sub'].astype(str) + '_' + df[
        'template_rank'].astype(str)  # 合并字段：根据sku，大等级，rank 合并

    df_cnt = df.groupby(by='concat_leve_rank')['limit_rate'].agg(
        {'level_limit_cnt': 'count', 'limit_max_rate': 'max', 'limit_min_rate': 'min'}).reset_index()  # 计算出等级里面 最大下压的比率
    df = df.merge(df_cnt, how='left', on='concat_leve_rank')
    df.loc[df.level_limit_cnt > 1, 'final_price'] = df.loc[df.level_limit_cnt > 1, 'process_price_last'] / df.loc[
        df.level_limit_cnt > 1, 'limit_max_rate']  # 相同大等级内且相同排序的，  所有等级按照最大下压处理

    # DDD=df.loc[df.concat_leve_rank.isin(['104897_H_15','1057761_H_15'])]
    df = df[['product_sku_key', 'product_level_key',
             'final_price', 'limit_flag', 'limit_rate', 'concat_leve_rank',
             'level_limit_cnt', 'limit_max_rate', 'limit_min_rate']]

    final_df = reverse_df1.merge(df, how='left', on=['product_sku_key', 'product_level_key'])  # 拼接e等级以下的处理数据

    final_df.loc[final_df.final_price.isnull(), 'final_price'] = final_df.loc[
        final_df.final_price.isnull(), 'process_price_last']

    match_a2_level_price = final_df.loc[
        final_df.product_level_name.isin(['A+']), ['product_sku_key', 'product_level_name', 'final_price']]
    match_a2_level_price.rename(columns={'final_price': 'a2_price', 'product_level_name': 'product_level_name_a2'},
                                inplace=True)

    final_dfa2 = final_df.merge(match_a2_level_price, how='left', on=['product_sku_key'])
    final_dfa2['y_pred'] = final_dfa2['final_price']
    final_dfa2 = final_dfa2.merge(purchase_df, how='left', on='product_sku_key')
    final_dfa2['purchase_flag'] = final_dfa2.purchase_flag.fillna(0)

    final_dfa2['y_pred'] = final_dfa2.apply(S_A_base_a2_fun, axis=1)
    final_dfa2 = final_dfa2.drop_duplicates()
    final_dfa2 = final_dfa2[CHECK_COLUMNS_LIST]
    save_price_df(final_dfa2)
    final_dfa2.to_csv(FILE_DIR + 'sku2_final_price_tablet.csv', encoding='utf-8-sig')

    logger.info('ds')


def save_price_df(predict_data):
    dt = datetime.datetime.strptime(time.strftime('%Y-%m-%d'), '%Y-%m-%d') + datetime.timedelta(days=1)
    predict_data['date'] = dt.strftime('%Y-%m-%d')

    delete_rate_sql = """
    delete from price_prediction_level2_price_reverse_tablet_v1 where date=DATE_FORMAT(date_sub(curdate(),interval 10 day),'%Y%m%d') 
    """
    mysql_prediction_processor.execute_sql(delete_rate_sql)
    delete_td_rate_sql = """
            delete from price_prediction_level2_price_reverse_tablet_v1 where date=DATE_FORMAT(date_add(curdate(),interval 1 day),'%Y%m%d') 
            """
    mysql_prediction_processor.execute_sql(delete_td_rate_sql)

    insert_sql = """
    INSERT INTO price_prediction_level2_price_reverse_tablet_v1(date, product_category_id, product_category_name,product_brand_id,
       product_brand_name,product_key, product_name, product_sku_name, product_sku_key, product_level_name, level_sub, template_rank, rank,product_level_key, predict_origin,process_price_res,process_price_inverse_begin,
       process_price_last,process_price,thisprice,saleprice,final_price, forecast_reference_price, price_0_7, sale_num_0_7, price_8_14,
       sale_num_8_14, price_15_21, sale_num_15_21, price_22_42,sale_num_22_42, count, 
       price_3, price_2, price_1,  qty, mean_rate, reverse,reverse_cnt,sku_e_level_min_rank, sku_level_max_price,
       sku_e_level_min_price,  limit_flag, limit_rate,concat_leve_rank, level_limit_cnt, limit_max_rate,limit_min_rate,product_level_name_a2,a2_price,y_pred,purchase_channel_name,purchase_flag)
    VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,  %s,%s, %s, %s, %s, %s, %s, %s,  %s,%s, %s, %s, %s , %s,%s,
     %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """

    predict_data['limit_min_rate'] = predict_data['limit_min_rate'].apply(lambda x: round(x, 5))
    predict_data['limit_max_rate'] = predict_data['limit_max_rate'].apply(lambda x: round(x, 5))
    predict_data['y_pred'] = predict_data['y_pred'].apply(lambda x: round(x, 5))
    predict_data['a2_price'] = predict_data['a2_price'].apply(lambda x: round(x, 5))

    predict_data.fillna(-1, inplace=True)
    mysql_prediction_processor.execute_insert_sql(insert_sql,
                                                  predict_data[insert_mysql_columns

                                                  ].to_records(index=False).tolist())

    # save_data_to_gp(predict_data)
    logger.info('所有倒挂数据mysql 存储完成！')


def insert_data_to_incre(date):
    dt = check_date_str(date) + datetime.timedelta(days=1)
    # today = datetime.date.today();
    insert_sql = """
        INSERT INTO price_prediction_level2_price_reverse_tablet_v1(date, product_category_id, product_category_name,product_brand_id,
           product_brand_name,product_key, product_name, product_sku_name, product_sku_key, product_level_name, level_sub, template_rank, rank,product_level_key, predict_origin,process_price_res,process_price_inverse_begin,
           process_price_last,process_price,thisprice,saleprice,final_price, forecast_reference_price, price_0_7, sale_num_0_7, price_8_14,
           sale_num_8_14, price_15_21, sale_num_15_21, price_22_42,sale_num_22_42, count, 
           price_3, price_2, price_1,  qty, mean_rate, reverse,reverse_cnt,sku_e_level_min_rank, sku_level_max_price,
           sku_e_level_min_price,  limit_flag, limit_rate,concat_leve_rank, level_limit_cnt, limit_max_rate,limit_min_rate,product_level_name_a2,a2_price,y_pred,purchase_channel_name,purchase_flag)
        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,  %s,%s, %s, %s, %s, %s, %s, %s,  %s,%s, %s, %s, %s , %s,%s,
         %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

    today = format_date_string(dt)
    query_sql = """select DATE_FORMAT(date_add(curdate(),interval 1 day),'%Y%m%d') as date, product_category_id, product_category_name,product_brand_id,
       product_brand_name,product_key, product_name, product_sku_name, product_sku_key, product_level_name, level_sub, template_rank, rank,product_level_key, predict_origin,process_price_res,process_price_inverse_begin,
       process_price_last,process_price,thisprice,saleprice,final_price, forecast_reference_price, price_0_7, sale_num_0_7, price_8_14,
       sale_num_8_14, price_15_21, sale_num_15_21, price_22_42,sale_num_22_42, count, 
       price_3, price_2, price_1,  qty, mean_rate, reverse,reverse_cnt,sku_e_level_min_rank, sku_level_max_price,
       sku_e_level_min_price,  limit_flag, limit_rate,concat_leve_rank, level_limit_cnt, limit_max_rate,limit_min_rate,product_level_name_a2,a2_price,y_pred,purchase_channel_name,purchase_flag
                        from price_prediction_level2_price_reverse_tablet_v1 where date=DATE_FORMAT(date_add(curdate(),interval 0 day),'%Y%m%d') """
    lsql = query_sql
    df_all_data = mysql_prediction_processor.load_sql(lsql)

    if (df_all_data.shape[0] > 0):
        mysql_prediction_processor.execute_sql(
            "delete from price_prediction_level2_price_reverse_tablet_v1 where date=DATE_FORMAT(date_add(curdate(),interval 1 day),'%Y%m%d')")

        # df_all_data['create_date']=today
        df_all_data = df_all_data.fillna(-1)
        mysql_prediction_processor.execute_insert_sql(insert_sql, df_all_data[insert_mysql_columns].to_records(
            index=False).tolist())
        df_size = df_all_data.shape[0]
        logger.info('(通知消息)sku2手机倒挂率不处理，拷贝数据完成! 时间:{} date={} 数据 category_id=1 ，dataSize={}'.format(today, date,
                                                                                                    df_all_data.shape[
                                                                                                        0]))
        # feishu_messager.send_message('(通知消息)sku2手机数据插入完成! 时间:{} date={} category_id@{} ，dataSize={} '.format(today,date,category_id, df_all_data.shape[0]))
        # save_data_to_gp(df_all_data)
        return df_size
    else:
        logger.info('(通知消息)sku2手机数据插入表数为空! 时间@{}  date={}数据  category_id=1 ，dataSize={} '.format(today, date,
                                                                                                 df_all_data.shape[0]))
        # feishu_messager.send_message(
        #     '(通知消息)sku2手机数据插入表数为空! 时间@{}  date={} category_id@{} ，dataSize={} '.format(today,date, category_id, df_all_data.shape[0]))
    return 0


if __name__ == '__main__':
    try:

        if (len(sys.argv) > 1):
            date = sys.argv[1]
            today = format_date(date)
        else:
            today = get_today();
        # today = get_today()
        md_date = format_date_string(today)
        mysql_price_num = 0
        if (today.weekday() in [0,1,2, 3,4,5, 6]):
            t1 = time.time()
            combine_reverse_fun()
            process_e_price()
            logger.info('tablet sku2_level_e_price_process end！！！')
            feishu_messager.send_message('平板苹果 sku2_level_e_price_process tablet 等级倒挂+属性倒挂+规则处理完成！date={} time consuming={}'.format(md_date,(time.time() - t1) / 60))
            logger.info('process sku2_level_e_price_process use time @{}'.format((time.time() - t1) / 60))
        else:
            mysql_price_num = insert_data_to_incre(md_date)
            logger.info('sku2_level_e_price_process tablet 等级倒挂+属性倒挂+规则处理完成！')
            print(1)

        logger.info('sku2_level_e_price_process tablet 等级倒挂+属性倒挂+规则处理完成！')
        exit(0)

    except Exception as e:

        logger.critical('sku2 等级倒挂+属性倒挂+规则处理失败：@{}'.format(e))
        feishu_messager.send_message('sku2 tablet等级倒挂+属性倒挂+规则处理失败：@{}'.format(e))
        raise TypeError('sku2平板苹果 等级倒挂+属性倒挂+规则处理失败:') from e
        exit(1)
